﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class LogDAO
    {
        public LogDAO() { }

        Log log = new Log();
        PPARDAO pdao = new PPARDAO();

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Log model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "LOG");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into LOG(");
            strSql.Append("ID,LOGDATE,USERS,PPARNO,PARTNO,ACTION)");
            strSql.Append(" values (");
            strSql.Append(":ID,:LOGDATE,:USERS,:PPARNO,:PARTNO,:ACTION)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":LOGDATE", OracleType.DateTime),
					new OracleParameter(":USERS", OracleType.VarChar,50),
					new OracleParameter(":PPARNO", OracleType.VarChar,50),
					new OracleParameter(":PARTNO", OracleType.VarChar,50),
					new OracleParameter(":ACTION", OracleType.VarChar,500)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.LOGDATE;
            parameters[2].Value = model.USERS;
            parameters[3].Value = model.PPARNO;
            parameters[4].Value = model.PARTNO;
            parameters[5].Value = model.ACTION;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(Log model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update LOG set ");
            strSql.Append("ID=:ID,");
            strSql.Append("LOGDATE=:LOGDATE,");
            strSql.Append("USERS=:USERS,");
            strSql.Append("PPARNO=:PPARNO,");
            strSql.Append("PARTNO=:PARTNO,");
            strSql.Append("ACTION=:ACTION");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":LOGDATE", OracleType.DateTime),
					new OracleParameter(":USERS", OracleType.VarChar,50),
					new OracleParameter(":PPARNO", OracleType.VarChar,50),
					new OracleParameter(":PARTNO", OracleType.VarChar,50),
					new OracleParameter(":ACTION", OracleType.VarChar,500)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.LOGDATE;
            parameters[2].Value = model.USERS;
            parameters[3].Value = model.PPARNO;
            parameters[4].Value = model.PARTNO;
            parameters[5].Value = model.ACTION;

          return  DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from LOG ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

          return  DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Log GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,LOGDATE,USERS,PPARNO,PARTNO,ACTION from LOG ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            Log model = new Log();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LOGDATE"].ToString() != "")
                {
                    model.LOGDATE = DateTime.Parse(ds.Tables[0].Rows[0]["LOGDATE"].ToString());
                }
                model.USERS = ds.Tables[0].Rows[0]["USERS"].ToString();
                model.PPARNO = ds.Tables[0].Rows[0]["PPARNO"].ToString();
                model.PARTNO = ds.Tables[0].Rows[0]["PARTNO"].ToString();
                model.ACTION = ds.Tables[0].Rows[0]["ACTION"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select rownum,ID,LOGDATE,USERS,PPARNO,PARTNO,ACTION ");
            strSql.Append(" FROM LOG ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 写入日志
        /// </summary>
        /// <param name="username">操作人</param>
        /// <param name="action">操作内容</param>
        /// <returns></returns>
        public int WriteLog(string username, string action)
        {
            log.ACTION = action;
            log.LOGDATE = DateTime.Now;
            log.PARTNO = "";
            log.PPARNO = "";
            log.USERS = username;
            return Add(log);
        }

        /// <summary>
        /// 写入日志
        /// </summary>
        /// <param name="username">操作人</param>
        /// <param name="action">操作内容</param>
        /// <param name="RID">PPARID</param>
        /// <returns></returns>
        public int WriteLog(string username, string action, string RID)
        {
            DataTable dt = pdao.GetNoForLog(RID);
            log.ACTION = action;
            log.LOGDATE = DateTime.Now;
            log.PARTNO = "";
            log.PPARNO = "";
            log.USERS = username;
            if (dt.Rows.Count > 0)
            {
                log.PPARNO = dt.Rows[0][0].ToString();
                log.PARTNO = dt.Rows[0][1].ToString();
            }
            return Add(log);
        }

        public DataSet GetReport(string sqlwhere,string sqlwhere2)
        {
            string sql = "select users,count(users) as num from log group by users";
            sql = string.Format(@"select * from (
select 'refused' as name,count(*) as num,1 as type from ppar where status=7 and finalresult='G' {1}
union 
select 'condtionally approved' as name,count(*) as num,2 from ppar where status=7 and finalresult='Y' {1}
union 
select 'apprvoed' as name,count(*) as num,3 from ppar where status=7 and finalresult='R' {1}
union 
select 'no. of parts arrived for inspection' as name,count(*) as num,4 from log where action='Submit Sampling Capbility:PPAR ready for sampling' {0} group by pparno
union 
select 'no. of inspections started' as name,0 num,5 from log 
union 
select 'no. of inspections finished' as name,0 num,6 from log 
union 
select 'planned no. of inspection finished' as name,0 num,7 from log 
) order by type", sqlwhere,sqlwhere2);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// refused
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport1(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(ppardate)>= to_date('"+date1+"','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(ppardate)<to_date('"+date2+"','yyyy-mm-dd') ";
            string sql = string.Format(@"select 'cw'||to_char(ppardate,'IW') cw,count(*) count from ppar where 
      status=7 and finalresult='G' {0} group by to_char(ppardate,'IW')　　order by to_char(ppardate,'IW')",sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// condtionally approved
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport2(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(ppardate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(ppardate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@"select 'cw'||to_char(ppardate,'IW') cw,count(*) count from ppar where 
      status=7 and finalresult='Y' {0} group by to_char(ppardate,'IW')　　order by to_char(ppardate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// apprvoed
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport3(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(ppardate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(ppardate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@"select 'cw'||to_char(ppardate,'IW') cw,count(*) count from ppar where 
      status=7 and finalresult='R' {0} group by to_char(ppardate,'IW')　　order by to_char(ppardate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// no. of parts arrived for inspection
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport4(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(logdate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(logdate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@" select 'cw'||to_char(logdate,'IW') cw,count(*) count from
  log where action='Submit Sampling Capbility:PPAR ready for sampling' {0}  group by to_char(logdate,'IW')　order by to_char(logdate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// no. of inspections started
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport5(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(logdate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(logdate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@" select 'cw'||to_char(logdate,'IW') cw,count(*) count from
  log where action='Submit (Inspection)' {0}  group by to_char(logdate,'IW')　order by to_char(logdate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// no. of inspections finished
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport6(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(logdate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(logdate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@" select 'cw'||to_char(logdate,'IW') cw,count(*) count from
  log where action='Save PPAR' {0}  group by to_char(logdate,'IW')　order by to_char(logdate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// planned no. of inspection finished
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <returns></returns>
        public DataSet GetReport7(string date1, string date2)
        {
            string sqlwhere = "";
            if (date1 != null)
                sqlwhere += "  and  trunc(logdate)>= to_date('" + date1 + "','yyyy-mm-dd') ";
            if (date2 != null)
                sqlwhere += "  and trunc(logdate)<to_date('" + date2 + "','yyyy-mm-dd') ";
            string sql = string.Format(@" select 'cw'||to_char(logdate,'IW') cw,count(*) count from
  log where action='Submit PPAR' {0}  group by to_char(logdate,'IW')　order by to_char(logdate,'IW')", sqlwhere);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// InspectionReport 本周前三周
        /// </summary>
        /// <returns></returns>
        public DataSet GetReportSum()
        {
            string sql = @"select t.cw,sum(t.c1) c1,sum(t.c2) c2,sum(t.c3) c3,sum(t.c4) c4,sum(t.c5) c5,sum(t.c6) c6,sum(t.c7) c7 from (
      (select 'cw'||to_char(endtime,'IW') cw,count(*) c1,0 c2,0 c3,0 c4,0 c5,0 c6,0 c7  from ppar where 
      status=7 and finalresult='R' and endtime >=trunc(next_day(sysdate-22,1)) and endtime<=trunc(next_day(sysdate-8,1)+6)  group by to_char(endtime,'IW')　　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,count(*) c2,0 c3,0 c4,0 c5,0 c6,0 c7 from ppar where 
      status=7 and finalresult='Y' and endtime >=trunc(next_day(sysdate-22,1)) and endtime<=trunc(next_day(sysdate-8,1)+6)  group by to_char(endtime,'IW')　　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,0 c2,count(*) c3,0 c4,0 c5,0 c6,0 c7  from ppar where 
      status=7 and finalresult='G' and endtime >=trunc(next_day(sysdate-22,1)) and endtime<=trunc(next_day(sysdate-8,1)+6)  group by to_char(endtime,'IW') )
      union all
      ( select 'cw'||to_char(receivedate,'IW') cw,0 c1,0 c2,0 c3,count(*) c4,0 c5,0 c6,0 c7 from
  SIGN where 1=1  and receivedate >=trunc(next_day(sysdate-22,1)) and receivedate<=trunc(next_day(sysdate-8,1)+6)   group by to_char(receivedate,'IW')　)
      union all
      (select 'cw'||to_char(logdate,'IW') cw,0 c1,0 c2,0 c3,0 c4,count(*) c5,0 c6,0 c7  from
  log where action='Submit Sampling Capbility:PPAR ready for sampling' and logdate >=trunc(next_day(sysdate-22,1)) and logdate<=trunc(next_day(sysdate-8,1)+6)  group by to_char(logdate,'IW')　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,0 c2,0 c3,0 c4,0 c5,count(*) c6,0 c7  from ppar where 
      status=7  and endtime >=trunc(next_day(sysdate-22,1)) and endtime<=trunc(next_day(sysdate-8,1)+6)  group by to_char(endtime,'IW')　　)
      union all
     (select 'cw'||to_char(ppardate+30,'IW') cw,0 c1,0 c2,0 c3,0 c4,0 c5,0 c6,count(*) c7  from ppar where 1=1
     and ppardate+30 >=trunc(next_day(sysdate-22,1)) and ppardate+30<=trunc(next_day(sysdate-8,1)+6) 
        group by to_char(ppardate+30,'IW') ) order by cw
      )t group by t.cw order by t.cw";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// InspectionReport 指定时间
        /// </summary>
        /// <returns></returns>
        public DataSet GetReportSum(string date1, string date2)
        {
            string sql = string.Format(@"select t.cw,sum(t.c1) c1,sum(t.c2) c2,sum(t.c3) c3,sum(t.c4) c4,sum(t.c5) c5,sum(t.c6) c6,sum(t.c7) c7 from (
      (select 'cw'||to_char(endtime,'IW') cw,count(*) c1,0 c2,0 c3,0 c4,0 c5,0 c6,0 c7  from ppar where 
      status=7 and finalresult='R' and trunc(endtime) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(endtime)<=to_date( '{1}', 'yyyy-mm-dd ')  group by to_char(endtime,'IW')　　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,count(*) c2,0 c3,0 c4,0 c5,0 c6,0 c7 from ppar where 
      status=7 and finalresult='Y' and trunc(endtime) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(endtime)<=to_date( '{1}', 'yyyy-mm-dd ')  group by to_char(endtime,'IW')　　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,0 c2,count(*) c3,0 c4,0 c5,0 c6,0 c7  from ppar where 
      status=7 and finalresult='G' and trunc(endtime) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(endtime)<=to_date( '{1}', 'yyyy-mm-dd ')  group by to_char(endtime,'IW') )
      union all
      ( select 'cw'||to_char(receivedate,'IW') cw,0 c1,0 c2,0 c3,count(*) c4,0 c5,0 c6,0 c7 from
  SIGN where 1=1  and trunc(receivedate) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(receivedate)<=to_date( '{1}', 'yyyy-mm-dd ')   group by to_char(receivedate,'IW')　)
      union all
      (select 'cw'||to_char(logdate,'IW') cw,0 c1,0 c2,0 c3,0 c4,count(*) c5,0 c6,0 c7  from
  log where action='Submit Sampling Capbility:PPAR ready for sampling' and trunc(logdate) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(logdate)<=to_date( '{1}', 'yyyy-mm-dd ')  group by to_char(logdate,'IW')　)
      union all
      (select 'cw'||to_char(endtime,'IW') cw,0 c1,0 c2,0 c3,0 c4,0 c5,count(*) c6,0 c7  from ppar where 
      status=7  and trunc(endtime) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(endtime)<=to_date( '{1}', 'yyyy-mm-dd ')  group by to_char(endtime,'IW')　　)
      union all
     (select 'cw'||to_char(ppardate+30,'IW') cw,0 c1,0 c2,0 c3,0 c4,0 c5,0 c6,count(*) c7  from ppar where 1=1
     and trunc(ppardate+30) >=to_date( '{0}', 'yyyy-mm-dd ') and trunc(ppardate+30)<=to_date( '{1}', 'yyyy-mm-dd ') 
        group by to_char(ppardate+30,'IW') ) order by cw
      )t group by t.cw order by t.cw",date1,date2);
            return DbHelperOra.Query(sql);
        }
    }
}
